const express = require('express')
const router = express.Router()
const mysql = require('mysql')
const {env} = require('../dataBase')
// const env = {
//     host: "111.231.22.97",
//     user: "cqbase",
//     password: "MZM5j38RNCjdPRP7",
//     database: "cqbase", //数据库名字
//     multipleStatements: true //允许多条sql语句查询
// }


//获取首页信息
//获取比赛信息
const getMatch = () => {
    return new Promise((resolve, reject) => {
        let pool = mysql.createPool(env)
        //首页截取最近日期两个赛事
        let sql = 'select * from matchlist ORDER BY date DESC;'
        pool.query(sql, [], (err, result) => {
            try {
                const arr = result.slice(0, 2)
                resolve(arr.reverse())
            } catch {
                resolve([])
            }

        })
    })
}
//信息咨询
var pageSize = 10 //每次加载10条
const getinfo = (req) => {
    return new Promise((resolve, reject) => {
        let pool = mysql.createPool(env)
        let pageNo = Number(req.query['pageNo'])
        let offset = (pageNo - 1) * pageSize;
        //首页截取最近日期两个信息 每次截取10条最新
        let sql = 'select * from infolist ORDER BY date DESC limit ?,?;select COUNT(*) from infolist'
        pool.query(sql, [offset, pageSize], (err, result) => {
            try {
                // resolve(result)
                resolve({
                    result:result[0],
                    total:result[1][0]['COUNT(*)']
                })
            } catch {
                resolve([])
            }
        })
    })
}
/* 
@ 2025/3/17修改
首页轮播放置新闻置顶5条,可点击跳转,轮播管理暂时取消
*/
//首页轮播
const getcarousel = () => {
    //首页轮播截取5条
    return new Promise((resolve, reject) => {
        let pool = mysql.createPool(env)
        let sql='select * from carousel ORDER BY date DESC'
        pool.query(sql,(err,resl)=>{
            let size=Number(resl[0]['count'])
            pool.query('select * from infolist ORDER BY date DESC limit ?,?',[0,size],(err,resp)=>{
                try{
                    resolve(resp)
                }catch{
                    resolve([])
                }
            })
        })
    })
}
const getHomePage = async (req, res) => {
    const matchArr = await getMatch()
    const info = await getinfo(req)
    const carouselArr=await getcarousel()
    res.send({
        code:200,
        msg:'success',
        data:{
            matchArr:matchArr,
            info:info,
            carouselArr:carouselArr,
        }
    })
}
router.get('/homePage', (req, res) => {
    getHomePage(req, res)
})




//搜索 包括搜索标题|球员|教练|球队(暂时搜索标题,球队)

const searchTitle=(req)=>{
    return new Promise((resolve,reject)=>{
        let pool=mysql.createPool(env)
        let sql=`select * from infolist where title  like '%${req.query['keyword']}%'`
        pool.query(sql,[],(err,result)=>{
            try{
                resolve(result)
            }catch{
                console.log(err,"error")
            }
        })
    })
}
const searchTeam=(req)=>{
    return new Promise((resolve,reject)=>{
        let pool=mysql.createPool(env)
        let sql=`select * from teamlist where teamName  like '%${req.query['keyword']}%'`
        pool.query(sql,[],(err,result)=>{
            try{
                resolve(result)
            }catch{
                console.log(err,"error")
            }
        })
    })
}

const getLargest=async(req,res)=>{
    /*  
    标题+球员+主教练
    */
     let arrs1= await searchTitle(req)
     res.send({
        code:0,
        msg:'success',
        data:{
            arrs1:arrs1,
        }
     })
}
router.get('/getLargest',(req,res)=>{
      getLargest(req,res)
})











module.exports = router